﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL.Entity;
using E_ICS;

namespace ECS.DAL.Component
{
    public class DimensionRecordComponent : BaseSQLModel
    {
        #region private properties
        private const string SQL_QUERY = "SELECT [dimension_id], [slip_id], [dimension_number], [length], [width] " +
                                    ",[height], [piece_count], [create_date], [create_by] " +
                                    " FROM [dbo].[t_eics_dimension] "; //ORDER BY [create_date]";

        private const string SQL_PROC_QUERY = " ";

        private const string SQL_PARAM_SEARCH_SLIP_ID = " WHERE [slip_id]=@slip_id ";

        private const string SQL_INSERT = " ";

        private const string SQL_PROC_INSERT = "usp_InsertDimensionRecord";
        //@AppId,  " +
        //" @SlipId, @DimensionNumber, @Length, @Weight, @Height, @PeiceCount, " +
        //" @Device, @UserId "; 

        private const string SQL_UPDATE = " ";

        private const string SQL_PROC_UPDATE = "usp_UpdateDimensionRecord";

        private const string SQL_DELETE = " ";

        private const string SQL_PROC_DELETE = "usp_DeleteDimensionRecord";
        #endregion

        #region public properties
        /// <summary>
        /// Error result
        /// </summary>
        public ErrorInfo ErrorMessage { set; get; }
        #endregion

        #region constructor
        public DimensionRecordComponent()
        {
            ErrorMessage = new ErrorInfo();
        }
        #endregion

        #region private methods
        /// <summary>
        /// clear error message
        /// </summary>
        private void ClearErrorMessage()
        {
            ErrorMessage.Status = string.Empty;
            ErrorMessage.ErrorCode = string.Empty;
            ErrorMessage.Message = string.Empty;
        }
        #endregion

        #region public mehods
        /// <summary>
        /// get Eics setup data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            /*"SELECT [dimension_id], [slip_id], [dimension_number], [length], [width] " +
                                    ",[height], [piece_count], [create_date], [create_by] " +
                                    " FROM [dbo].[t_eics_dimension] "*/
            DimensionRecord param = (DimensionRecord)parameter;
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                //sqlString.Append(SQL_PROC_QUERY);
                sqlString.Append(SQL_QUERY);

                if (!string.IsNullOrEmpty(param.SlipId))
                {
                    sqlString.Append(SQL_PARAM_SEARCH_SLIP_ID);
                    sqlString.Replace("@slip_id", BaseSQLModel.SqlWithQoute(param.SlipId));
                }

                entities.Open();
                Console.WriteLine(sqlString.ToString());
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.Text;
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DimensionRecord data = new DimensionRecord();
                    data.DimensionId = base.GetString(0, reader);
                    data.SlipId = base.GetString(1, reader);
                    data.DimensionNumber = (int)base.GetInt(2, reader);
                    data.Length = (float)base.GetDouble(3, reader);
                    data.Width = (float)base.GetDouble(4, reader);
                    data.Height = (float)base.GetDouble(5, reader);
                    data.PieceCount = (int)base.GetInt(6, reader);
                    data.CreateDate = (DateTime)base.GetDate(7, reader);
                    data.CreateBy = base.GetString(8, reader);
                    data.NotChangeObject();
                    items.Add(data);
                }
            }
            return items;
        }

        public override string GetInsertSQL(ObjectContext param)
        {
            /*@AppId, @SlipId, @SlipId, @DimensionNumber, @Length, @Weight, @Height, @PeiceCount, @Device, @UserId "; */
            DimensionRecord parameter = (DimensionRecord)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_INSERT);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@SlipId", BaseSQLModel.SqlWithQoute(parameter.SlipId));
            //sqlString.Replace("@DimensionNumber", BaseSQLModel.SqlWithoutQoute(parameter.WeightNumber));
            //sqlString.Replace("@Length", BaseSQLModel.SqlWithoutQoute(parameter.GrossWeight));
            //sqlString.Replace("@Weight", BaseSQLModel.SqlWithoutQoute(parameter.TareWeight));
            //sqlString.Replace("@Height", BaseSQLModel.SqlWithoutQoute(parameter.NetWeight));
            //sqlString.Replace("@PeiceCount", BaseSQLModel.SqlWithoutQoute(parameter.PalletCount));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            DimensionRecord parameter = (DimensionRecord)param;
            StringBuilder sqlString = new StringBuilder();
            //sqlString.Append(SQL_UPDATE);
            sqlString.Append(SQL_PROC_UPDATE);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            //sqlString.Replace("@station_id", BaseSQLModel.SqlWithQoute(parameter.StationId));
            //sqlString.Replace("@type", BaseSQLModel.SqlWithQoute(parameter.SetupType));
            //sqlString.Replace("@setting_name", BaseSQLModel.SqlWithQoute(parameter.SettingName));
            //sqlString.Replace("@setting_value_string", BaseSQLModel.SqlWithQoute(parameter.ValueString));
            //sqlString.Replace("@setting_value_number", parameter.ValueNumber == null ? "NULL" : parameter.ValueNumber.ToString());
            //sqlString.Replace("@setting_value_datetime", parameter.ValueDate == null ? "NULL" : BaseSQLModel.SqlWithQoute(string.Format("{0:yyyy-MM-dd}", parameter.ValueDate)));
            ////sqlString.Replace("@create_date", BaseSQLModel.SqlWithQoute(parameter.CreateDate.ToString("yyyy-MM-dd")));
            ////sqlString.Replace("@create_by", BaseSQLModel.SqlWithQoute(parameter.CreateBy));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            DimensionRecord parameter = (DimensionRecord)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_DELETE);
            //sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
            DataSet ds = new DataSet();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));

                    DbDataAdapter adapter = entities.CreateDataAdapter(GetInsertSQL(param));
                    adapter.Fill(ds, "Result");
                    //entities.SetCommand(cmd);
                    //int m_iRecordChanged = entities.SaveChanges();
                    //if (m_iRecordChanged < 1)
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                        ErrorMessage.ID = string.Empty;
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("weight record update unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            DimensionRecord parameter = (DimensionRecord)param;
            DataSet ds = new DataSet();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                ClearErrorMessage();
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetDeleteSQL(param));
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.CommandText = sqlCmd;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDimensionID", parameter.DimensionId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                    DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                    adapter.Fill(ds);
                    
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        //if (!ds.Tables[0].Rows[0]["Status"].ToString().Equals("PASS"))
                        //{
                        //    string errorMessage = string.Format("Status: {0} Error Code:{1}\r\nError:{2}", ds.Tables[0].Rows[0]["Status"].ToString(), ds.Tables[0].Rows[0]["ErrorCode"].ToString(), ds.Tables[0].Rows[0]["Message"].ToString());
                        //    throw new Exception(errorMessage);
                        //}
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// special case for store procedure to update data that return result as record
        /// </summary>
        /// <param name="items"></param>
        public override void SaveChanges(List<ObjectContext> items)
        {
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            DimensionRecord parameter = null;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DataSet ds = new DataSet();
                    DbCommand cmd = entities.CreateCommand(sqlCmd);
                    cmd.CommandType = CommandType.StoredProcedure;

                    DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
                    adapter.SelectCommand = cmd;
                    foreach (ObjectContext item in items)
                    {
                        parameter = (DimensionRecord)item;
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = GetInsertSQL(item);
                                textMessage = "Insert ";
                                cmd.CommandText = sqlCmd;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_intDimensionNumber", parameter.DimensionNumber));
                                cmd.Parameters.Add(entities.CreateParameter("@in_fltLength", parameter.Length));
                                cmd.Parameters.Add(entities.CreateParameter("@in_fltWidth", parameter.Width));
                                cmd.Parameters.Add(entities.CreateParameter("@in_fltHeight", parameter.Height));
                                cmd.Parameters.Add(entities.CreateParameter("@in_intPieceCount", parameter.PieceCount));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                          
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = GetUpdateSQL(item);
                                textMessage = "Update ";
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.CommandText = sqlCmd;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchDimensionID", parameter.DimensionId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_intDimensionNumber", parameter.DimensionNumber));
                                cmd.Parameters.Add(entities.CreateParameter("@in_fltLength", parameter.Length));
                                cmd.Parameters.Add(entities.CreateParameter("@in_fltWidth", parameter.Width));
                                cmd.Parameters.Add(entities.CreateParameter("@in_fltHeight", parameter.Height));
                                cmd.Parameters.Add(entities.CreateParameter("@in_intPieceCount", parameter.PieceCount));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                              
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = GetDeleteSQL(item);
                                textMessage = "Delete ";
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.CommandText = sqlCmd;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchDimensionID", parameter.DimensionId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            adapter.SelectCommand = cmd;
                            adapter.Fill(ds);
                            ClearErrorMessage();
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                //if (!ds.Tables[0].Rows[0]["Status"].ToString().Equals("PASS"))
                                //{
                                //    string errorMessage = string.Format("Status: {0} Error Code:{1}\r\nError:{2}", ds.Tables[0].Rows[0]["Status"].ToString(), ds.Tables[0].Rows[0]["ErrorCode"].ToString(), ds.Tables[0].Rows[0]["Message"].ToString());
                                //    throw new Exception(errorMessage);
                                //}
                                ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                                ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                                ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                            }
                            //item.NotChangeObject();
                        }
                    }
                }
                transaction.Complete();
                items.RemoveAll(o => o.GetObjectSate() == OBJECT_STATE.Delete);
                foreach (ObjectContext data in items)
                {
                    data.NotChangeObject();
                }
            }
        }
        #endregion
    }
}
